package com.ldy.core.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.ldy.core.vo.FieldVO;
import com.ldy.core.vo.TableVO;

/**
 * 数据库处理
 */
@Component
@Transactional
public class DatabaseUtil {

	private static final Logger logger = LoggerFactory.getLogger(DatabaseUtil.class);

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Autowired
	private DataSource dataSource;

	/**
	 * 获取数据库表元数据
	 */
	public TableVO getTable(String tableName) {
		List<TableVO> list = getTable(tableName, new String[] { "TABLE" });
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	/**
	 * 获取所有表
	 * 
	 * @return
	 */
	public List<TableVO> getTables() {
		return getTable("%", new String[] { "TABLE" });
	}

	private List<TableVO> getTable(String tableName, String[] types) {
		List<TableVO> result = new ArrayList<>();
		try (Connection connection = dataSource.getConnection()) {
			DatabaseMetaData metaData = connection.getMetaData();
			ResultSet tables = metaData.getTables(null, null, tableName, types);
			while (tables.next()) {
				String name = tables.getString("TABLE_NAME");
				String remarks = tables.getString("REMARKS");
				TableVO vo = new TableVO();
				vo.setName(name);
				vo.setRemarks(remarks);
				result.add(vo);
			}
			tables.close();
		} catch (SQLException e) {
			logger.error("Error retrieving database metadata", e);
		}
		return result;
	}

	/**
	 * 获取数据库表元数据
	 */
	public List<FieldVO> getFields(String tableName) {
		List<FieldVO> result = new ArrayList<FieldVO>();
		try (Connection connection = dataSource.getConnection()) {
			DatabaseMetaData metaData = connection.getMetaData();
			// 获取表中所有字段的信息
			ResultSet columns = metaData.getColumns(null, null, tableName, "%");
			ResultSet primaryKeys = metaData.getPrimaryKeys(null, null, tableName);
			List<String> primaryKey = new ArrayList<>();
			while (primaryKeys.next()) {
				String columnName = primaryKeys.getString("COLUMN_NAME");
				System.out.println("Primary Key Column: " + columnName);
				primaryKey.add(columnName);
			}

			while (columns.next()) {
				String columnName = columns.getString("COLUMN_NAME");
				String columnType = columns.getString("TYPE_NAME");
				String remarks = columns.getString("REMARKS");
				// 长度
				int precision = columns.getInt("COLUMN_SIZE");
				// 精度
				int scale = columns.getInt("DECIMAL_DIGITS");
				String defaultValue = columns.getString("COLUMN_DEF");
				boolean isNullable = columns.getInt("NULLABLE") == DatabaseMetaData.columnNullable;
				boolean isPrimaryKey = primaryKey.contains(columnName);
				logger.info(
						"  Column: {}, Type: {}, Remarks: {}, isPrimaryKey: {},defaultValue: {},isNullable: {}, precision:{}, scale:{} ",
						columnName, columnType, remarks, isPrimaryKey, defaultValue, isNullable, precision, scale);

				FieldVO vo = new FieldVO();
				vo.setName(columnName);
				vo.setTableName(tableName);
				vo.setDefaultValue(defaultValue);
				vo.setRemarks(remarks);
				vo.setType(columnType);
				vo.setSize(precision);
				vo.setAccuracy(scale);
				vo.setIsNullable(isNullable);
				vo.setIsPrimaryKey(isPrimaryKey);
				result.add(vo);
			}

			columns.close();
		} catch (SQLException e) {
			logger.error("Error retrieving database metadata", e);
		}
		return result;
	}

	/**
	 * 获取数据库表元数据
	 */
	public List<TableVO> getDatabaseMetaData() {
		List<TableVO> list = getTables();
		list.forEach(c -> {
			c.setFields(getFields(c.getName()));
		});
		return list;
	}

	/**
	 * 创建表
	 * 
	 * @param vo
	 */
	public void createTable(TableVO vo) {
		List<FieldVO> fields = vo.getFields();
		StringBuffer sb = new StringBuffer();
		for (FieldVO f : fields) {
			String dbTpye = FieldType.getDBTpye(f.getType(), f.getSize(), f.getAccuracy());
			sb.append(f.getName()).append(" ").append(dbTpye).append(" ")
					.append(f.getIsPrimaryKey() ? " PRIMARY KEY" : "").append(" ")
					.append(f.getIsNullable() ? "" : " NOT NULL").append(setDefault(f.getDefaultValue()))
					.append(" COMMENT").append(" '").append(f.getRemarks()).append("',");
		}
		// 创建表
		createTable(vo.getName(), sb.deleteCharAt(sb.length() - 1).toString(), vo.getRemarks());
	}

	/**
	 * 更新表字段
	 * 
	 * @param vo
	 */
	public void updateTable(TableVO vo) {
		TableVO table = getTable(vo.getId());
		if (!vo.getId().equals(vo.getName())) {
			// 表名修改了
			updateTable(vo.getId(), vo.getName(), vo.getRemarks());
		} else if (!vo.getRemarks().equals(table.getRemarks())) {
			// 更新备注
			updateTableRemarks(vo.getName(), vo.getRemarks());
		}
		List<FieldVO> fields = getFields(vo.getName());
		Map<String, FieldVO> map = new HashMap<String, FieldVO>();
		fields.stream().forEach(c -> map.put(c.getName(), c));
		// TODO 删除的字段
		for (FieldVO f : vo.getFields()) {
			String dbTpye = FieldType.getDBTpye(f.getType(), f.getSize(), f.getAccuracy());
			if (StringUtils.isEmpty(f.getId())) {
				// 新增
				addColumn(vo.getName(), f.getName(), dbTpye, f.getIsPrimaryKey(), f.getIsNullable(), f.getRemarks(),
						f.getDefaultValue());
			} else {
				// 更新
				FieldVO ff = map.get(f.getId());
				String oldTpye = FieldType.getDBTpye(ff.getType(), ff.getSize(), ff.getAccuracy());
				if (!f.getId().equals(f.getName())) {
					// 修改字段名了，需要先修改
					changeColumn(vo.getName(), f.getId(), f.getName(), dbTpye, f.getIsPrimaryKey(), f.getIsNullable(),
							f.getRemarks(), f.getDefaultValue());
				} else if (!(f.getIsPrimaryKey() == ff.getIsPrimaryKey() //
						&& f.getIsNullable() == ff.getIsNullable() //
						&& oldTpye.equals(dbTpye) //
						&& equals(f.getRemarks(), ff.getRemarks())
						&& equals(f.getDefaultValue(), ff.getDefaultValue()))) {
					updateColumn(vo.getName(), f.getName(), dbTpye, f.getIsPrimaryKey(), f.getIsNullable(),
							f.getRemarks(), f.getDefaultValue());
				}
			}
		}

	}

	/**
	 * 比较
	 * @param src
	 * @param dest
	 * @return
	 */
	public boolean equals(String src, String dest) {
		return (StringUtils.hasText(src) ? src : "").equals(StringUtils.hasText(dest) ? dest : "");
	}

	/**
	 * 设置默认值
	 * 
	 * @param src
	 * @return
	 */
	public static String setDefault(String src) {
		if (StringUtils.hasText(src)) {
			return String.format(" DEFAULT %s ", src);
		}
		return "";
	}

	/**
	 * 创建表
	 * 
	 * @param tableName
	 * @param columnDefinition
	 */
	public void createTable(String tableName, String columnDefinition, String tableRemarks) {
		String createTableSql = "CREATE TABLE " + tableName + " (" + columnDefinition + ") COMMENT = ?";
		jdbcTemplate.update(createTableSql, tableRemarks);
	}

	/**
	 * 修改表信息
	 * 
	 * @param oldTableName
	 * @param newTableName
	 * @param newRemarks
	 */
	public void updateTable(String oldTableName, String newTableName, String newRemarks) {
		String renameTableSql = "ALTER TABLE " + oldTableName + " RENAME TO " + newTableName;
		jdbcTemplate.execute(renameTableSql);

		// 更新表备注
		updateTableRemarks(newTableName, newRemarks);
	}

	public void addColumn(String tableName, String columnName, String columnType, boolean isPrimaryKey,
			boolean allowNull, String remarks, String defaultValue) {
		String addColumnSql = "ALTER TABLE " + tableName + " ADD COLUMN " + columnName + " " + columnType
				+ (isPrimaryKey ? " PRIMARY KEY" : "") + (allowNull ? "" : " NOT NULL") + setDefault(defaultValue)
				+ " COMMENT ?";

		jdbcTemplate.update(addColumnSql, remarks);
	}

	public void updateColumn(String tableName, String columnName, String newColumnType, boolean isPrimaryKey,
			boolean allowNull, String newRemarks, String defaultValue) {
		String updateColumnSql = "ALTER TABLE " + tableName + " MODIFY COLUMN " + columnName + " " + newColumnType
				+ (isPrimaryKey ? " PRIMARY KEY" : "") + (allowNull ? "" : " NOT NULL") + setDefault(defaultValue)
				+ " COMMENT ?";

		jdbcTemplate.update(updateColumnSql, newRemarks);
	}

	public void changeColumn(String tableName, String columnName, String newColumnName, String newColumnType,
			boolean isPrimaryKey, boolean allowNull, String newRemarks, String defaultValue) {
		String updateColumnSql = "ALTER TABLE " + tableName + " CHANGE " + columnName + " " + newColumnName + " "
				+ newColumnType + (isPrimaryKey ? " PRIMARY KEY" : "") + (allowNull ? "" : " NOT NULL")
				+ setDefault(defaultValue) + " COMMENT ?";
		jdbcTemplate.update(updateColumnSql, newRemarks);
	}

	public void deleteColumn(String tableName, String columnName) {
		String deleteColumnSql = "ALTER TABLE " + tableName + " DROP COLUMN " + columnName;
		jdbcTemplate.execute(deleteColumnSql);
	}

	public void deleteTable(String tableName) {
		String deleteTableSql = "DROP TABLE IF EXISTS " + tableName;
		jdbcTemplate.execute(deleteTableSql);
	}

	public List<Map<String, Object>> executeSqlQuery(String sql) {
		return jdbcTemplate.queryForList(sql);
	}

	private void updateTableRemarks(String tableName, String newRemarks) {
		String updateRemarksSql = "ALTER TABLE " + tableName + " COMMENT ?";
		jdbcTemplate.update(updateRemarksSql, newRemarks);
	}

	private void updateColumnRemarks(String tableName, String columnName, String newRemarks) {
		String updateRemarksSql = "ALTER TABLE " + tableName + " MODIFY COLUMN " + columnName + " COMMENT ?";
		jdbcTemplate.update(updateRemarksSql, newRemarks);
	}

	/**
	 * 字段类型
	 */
	public static enum FieldType {
		字符串("VARCHAR"), 整型("INT"), 长整型("BIGINT"), 浮点型("DECIMAL"), 日期("DATE"), 日期时间("TIMESTAMP"), 布尔("BOOLEAN"),
		文本("TEXT"), 长文本("LONGTEXT");

		private String type;

		FieldType(String type) {
			this.type = type;
		}

		public String getType() {
			return type;
		}

		public void setType(String type) {
			this.type = type;
		}

		public static String getDBTpye(String type, int size, int accuracy) {
			if (字符串.type.equalsIgnoreCase(type)) {
				return String.format("VARCHAR(%d)", size);
			} else if (浮点型.type.equalsIgnoreCase(type)) {
				return String.format("DECIMAL(%d,%d)", size, accuracy);
			}
			return type;
		}
	}

}
